/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package net.wofly.sale.stock.repository;

import java.util.List;

import net.wofly.sale.report.domain.ResourceAskDTO;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import net.wofly.sale.stock.domain.ResourceAsk;
import org.springframework.data.repository.query.Param;

public interface ResourceAskRepository
		extends PagingAndSortingRepository<ResourceAsk, String>, JpaSpecificationExecutor<ResourceAsk> {


	/**
	 * dd
	 *
	 * @param askNo dd
	 * @return
     */
	@Query(value = "SELECT CAST(SUBSTRING(askno, 9) as UNSIGNED INTEGER) FROM sale_stock_resourceask WHERE askno " +
			"LIKE :askNo ORDER BY CAST(askno as UNSIGNED INTEGER) DESC LIMIT 0, 1", nativeQuery = true)
	Long findLastAskNoForEveryday(@Param("askNo") String askNo);
	
	List<ResourceAsk> findByProcessInsIDIn(List<String> processInsIDs) ;
	
	ResourceAsk findByAskerAndStatus(String asker ,Integer status) ;
	
	ResourceAsk findByProcessInsID(String processInsID) ;

	/**
	 * 查询领料申请数量
	 *
	 * @param countDate
	 * @param countEndTime
     * @return
     */
	@Query(value = "select new ResourceAskDTO(ra.asker, u.name, ral.commodityTerminalID, ct.commodityTerminalName, sum(ral.askNum)) " +
			"from ResourceAsk ra, User u, ResourceAskList ral, CommodityTerminal ct " +
			"where ra.consignTime between :countDate and :countEndTime and ra.status = 40 " +
			"and ra.asker = u.userID and ra.resourceAskID = ral.resourceAskID and ral.commodityTerminalID = ct.commodityTerminalID " +
			"group by ra.asker, u.name, ral.commodityTerminalID, ct.commodityTerminalName ")
	List<ResourceAskDTO> findByCountDate(@Param("countDate") Long countDate, @Param("countEndTime") Long countEndTime);
}